-- FlowPlan数据库连接配置表结构
-- 为支持智能电子表格功能，需要添加数据库连接配置表

-- ==========================================
-- 13. 数据库连接配置表 (db_connection)
-- ==========================================

-- 表结构
CREATE TABLE db_connection (
    id BIGSERIAL PRIMARY KEY,
    connection_name VARCHAR(100) NOT NULL,
    db_type VARCHAR(20) NOT NULL,
    host VARCHAR(255) NOT NULL,
    port INTEGER NOT NULL,
    database_name VARCHAR(100) NOT NULL,
    username VARCHAR(100) NOT NULL,
    password_encrypted VARCHAR(500) NOT NULL,
    project_id BIGINT NOT NULL,
    created_by BIGINT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES project(id),
    FOREIGN KEY (created_by) REFERENCES sys_user(id)
);

-- 表注释
COMMENT ON TABLE db_connection IS '数据库连接配置表，存储用户配置的外部数据库连接信息';

-- 字段注释
COMMENT ON COLUMN db_connection.id IS '连接配置ID，主键，自增长';
COMMENT ON COLUMN db_connection.connection_name IS '连接名称，用户自定义的连接标识';
COMMENT ON COLUMN db_connection.db_type IS '数据库类型（MYSQL, POSTGRESQL, SQLSERVER, SQLITE, MARIADB）';
COMMENT ON COLUMN db_connection.host IS '数据库主机地址';
COMMENT ON COLUMN db_connection.port IS '数据库端口号';
COMMENT ON COLUMN db_connection.database_name IS '数据库名称';
COMMENT ON COLUMN db_connection.username IS '数据库用户名';
COMMENT ON COLUMN db_connection.password_encrypted IS '加密后的数据库密码';
COMMENT ON COLUMN db_connection.project_id IS '关联项目ID，关联project表';
COMMENT ON COLUMN db_connection.created_by IS '创建人ID，关联sys_user表';
COMMENT ON COLUMN db_connection.is_active IS '是否激活，标识连接配置是否可用';
COMMENT ON COLUMN db_connection.created_at IS '创建时间';
COMMENT ON COLUMN db_connection.updated_at IS '更新时间';

-- 索引定义
CREATE INDEX idx_db_connection_project_id ON db_connection(project_id);
CREATE INDEX idx_db_connection_created_by ON db_connection(created_by);
CREATE INDEX idx_db_connection_db_type ON db_connection(db_type);
CREATE UNIQUE INDEX uk_db_connection_name_project ON db_connection(connection_name, project_id);

-- ==========================================
-- 14. 数据表映射配置表 (table_mapping)
-- ==========================================

-- 表结构
CREATE TABLE table_mapping (
    id BIGSERIAL PRIMARY KEY,
    connection_id BIGINT NOT NULL,
    source_table_name VARCHAR(100) NOT NULL,
    target_table_name VARCHAR(100) NOT NULL,
    mapping_config JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (connection_id) REFERENCES db_connection(id)
);

-- 表注释
COMMENT ON TABLE table_mapping IS '数据表映射配置表，存储源数据库表与系统表的映射关系';

-- 字段注释
COMMENT ON COLUMN table_mapping.id IS '映射配置ID，主键，自增长';
COMMENT ON COLUMN table_mapping.connection_id IS '连接配置ID，关联db_connection表';
COMMENT ON COLUMN table_mapping.source_table_name IS '源数据库表名';
COMMENT ON COLUMN table_mapping.target_table_name IS '目标系统表名';
COMMENT ON COLUMN table_mapping.mapping_config IS '映射配置，JSON格式存储字段映射关系';
COMMENT ON COLUMN table_mapping.created_at IS '创建时间';
COMMENT ON COLUMN table_mapping.updated_at IS '更新时间';

-- 索引定义
CREATE INDEX idx_table_mapping_connection_id ON table_mapping(connection_id);
CREATE UNIQUE INDEX uk_table_mapping_source_target ON table_mapping(connection_id, source_table_name, target_table_name);

-- ==========================================
-- 15. 数据同步记录表 (data_sync_record)
-- ==========================================

-- 表结构
CREATE TABLE data_sync_record (
    id BIGSERIAL PRIMARY KEY,
    connection_id BIGINT NOT NULL,
    table_name VARCHAR(100) NOT NULL,
    sync_type VARCHAR(20) NOT NULL,
    record_count INTEGER,
    success_count INTEGER DEFAULT 0,
    fail_count INTEGER DEFAULT 0,
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    status VARCHAR(20) DEFAULT 'PENDING',
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (connection_id) REFERENCES db_connection(id)
);

-- 表注释
COMMENT ON TABLE data_sync_record IS '数据同步记录表，记录数据库同步操作的历史';

-- 字段注释
COMMENT ON COLUMN data_sync_record.id IS '同步记录ID，主键，自增长';
COMMENT ON COLUMN data_sync_record.connection_id IS '连接配置ID，关联db_connection表';
COMMENT ON COLUMN data_sync_record.table_name IS '同步的表名';
COMMENT ON COLUMN data_sync_record.sync_type IS '同步类型（FULL, INCREMENTAL）';
COMMENT ON COLUMN data_sync_record.record_count IS '记录总数';
COMMENT ON COLUMN data_sync_record.success_count IS '成功记录数';
COMMENT ON COLUMN data_sync_record.fail_count IS '失败记录数';
COMMENT ON COLUMN data_sync_record.start_time IS '同步开始时间';
COMMENT ON COLUMN data_sync_record.end_time IS '同步结束时间';
COMMENT ON COLUMN data_sync_record.status IS '同步状态（PENDING, RUNNING, COMPLETED, FAILED）';
COMMENT ON COLUMN data_sync_record.error_message IS '错误信息';
COMMENT ON COLUMN data_sync_record.created_at IS '创建时间';

-- 索引定义
CREATE INDEX idx_data_sync_record_connection_id ON data_sync_record(connection_id);
CREATE INDEX idx_data_sync_record_table_name ON data_sync_record(table_name);
CREATE INDEX idx_data_sync_record_status ON data_sync_record(status);
CREATE INDEX idx_data_sync_record_created_at ON data_sync_record(created_at);

-- 创建自动更新时间戳的函数
CREATE OR REPLACE FUNCTION update_db_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为需要自动更新时间戳的表创建触发器
CREATE TRIGGER update_db_connection_modtime 
    BEFORE UPDATE ON db_connection 
    FOR EACH ROW EXECUTE FUNCTION update_db_modified_column();

CREATE TRIGGER update_table_mapping_modtime 
    BEFORE UPDATE ON table_mapping 
    FOR EACH ROW EXECUTE FUNCTION update_db_modified_column();